package com.southgis.ibase.sql.builder;

import java.util.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.util.CollectionUtils;
import org.springframework.util.LinkedCaseInsensitiveMap;

import com.southgis.ibase.sql.builder.ClauseItem.SingleValue;
import com.southgis.ibase.sql.exception.TableNotExistException;
import com.southgis.ibase.sql.utils.DatabaseFunc;

/**
 * @description 基于对象的sql构建器
 * @date 2022/5/18
 */
public class SqlBuilder {
	/**
	 * 在select-fields、update-sets子句区段间
	 */
	public static final int BUILD_SECTION_SEUPDATE=0;
	/**
	 * 在条件区段间，包括where子句、having子句、on子句
	 */
	public static final int BUILD_SECTION_CONDITION=1;
	/**
	 * 在group by、order by子句区段间
	 */
	public static final int BUILD_SECTION_XBY=2;
	/**
	 * 在from、join子句区段间
	 */
	public static final int BUILD_SECTION_FROMJOIN=3;
	/**
	 * 在insert-into/fields、delete-form子句中
	 */
	public static final int BUILD_SECTION_DEINSERT=4;
	
	/**
	 * 正在构建where条件中
	 */
	private static final int COND_WHERE=1;
	/**
	 * 正在构建on条件中
	 */
	private static final int COND_ON=2;
	/**
	 * 正在构建having条件中
	 */
	private static final int COND_HAVING=3;
	/**
	 * 正在构建field字段值中
	 */
	private static final int COND_FIELD=4;
	
	/**
	 * 构建一个简单表对象，用于构建sql时的表对象表达
	 * @param tableName
	 * @param alias 可选。不设别名，可传空
	 * @return 表对象，SimpleTable类型
	 */
	public static Table createTable(String tableName, String alias)
	{
		return new SimpleTable(tableName, alias);
	}
	/**
	 * 构建一个简单表对象，用于构建sql时的表对象表达
	 * @param tableName
	 * @return
	 */
	public static Table createTable(String tableName)
	{
		return createTable(tableName, null);
	}

	/**
	 * 构建一个用sql语句描述的表对象，用于构建sql时的表对象表达
	 * @param sql
	 * @param alias 不设置别名时，只能用在子查询中。如果用于join，则需要设置别名
	 * @return 表对象，SqlTable类型
	 */
	public static Table createTable(SelectSqlBuilder sql, String alias)
	{
		return new SqlTable(sql, alias);
	}
	
	//README 生成条件，查询、更新、插入、删除语句的构造器
	
	/**
	 * 构建一个条件构造器
	 * @return 条件构造器
	 */
	public static ClauseBuilder clause()
	{
		return ClauseBuilder.produce();
	}

	/**
	 * 创建一个select语句构造器
	 * @return
	 */
	public static SelectSqlBuilder select()
	{
		return new SelectSqlBuilder();
	}
	
	/**
	 * 创建一个update语句构造器
	 * @param tbl 要更新的表对象
	 * @return
	 */
	public static UpdateSqlBuilder update(Table tbl)
	{
		return new UpdateSqlBuilder(tbl);
	}
	/**
	 * 创建一个update语句构造器
	 * @param tblName  要更新的表名
	 * @return
	 */
	public static UpdateSqlBuilder update(String tblName)
	{
		return new UpdateSqlBuilder(tblName);
	}
	
	/**
	 * 创建一个insert语句构造器
	 * @return
	 */
	public static InsertSqlBuilder insert()
	{
		return new InsertSqlBuilder();
	}	
	
	/**
	 * 创建一个delete语句构造器
	 * @return
	 */
	public static DeleteSqlBuilder delete()
	{
		return new DeleteSqlBuilder();
	}	
	//==================================
	
	//==================================
	
	//README 查询语句构造器定义
	/**
	 * 查询语句构造器定义
	 * @author dnnyo
	 *
	 */
	public static class SelectSqlBuilder
	{
		DatabaseFunc func;
		
		int conditionType=0;
		ClauseBuilder conditionBuilder=null;
		
		boolean distinct=false;
		List<TableColumn> selectCols=new ArrayList<>();
		Table fromTable;
		//当前选择表
		Table currentTarget;
		
		//连接方式、表对象、连接条件
		List<JoinTableInfo> joinTables=null;
		ClauseItem where;
		List<TableColumn> groupCols=null;
		ClauseItem having;
		
		SelectSqlBuilder unionParent;
		//要联合的子查询
		SelectSqlBuilder unionChild;
		//是否all联合子查询
		boolean unionAll=false;
		//排序字段、排序方式
		List<Pair<TableColumn, String>> orderCols=null;
		
		private List<JoinTableInfo> getJoinTables()
		{
			if(joinTables==null)
				joinTables=new ArrayList<>();
			return joinTables;
		}
		private List<TableColumn> getGroupCols()
		{
			if(groupCols==null)
				groupCols=new ArrayList<>();
			return groupCols;
		}
		private List<Pair<TableColumn, String>> getOrderCols()
		{
			if(orderCols==null)
				orderCols=new ArrayList<>();
			return orderCols;
		}
		
		SelectSqlBuilder()
		{
			
		}
		/**
		 * 与父语句做union
		 * @param parent
		 * @param all 是否union all
		 */
		SelectSqlBuilder(SelectSqlBuilder parent, boolean all)
		{
			this();
			parent.unionChild=this;
			parent.unionAll=all;
			this.unionParent=parent;
			this.func=parent.func;
		}
		/**
		 * 获得当前用于转换函数调用的适配类
		 * @return
		 */
		public DatabaseFunc getDatabaseFunc()
		{
			return func;
		}
		/**
		 * 用于转换函数调用的适配类
		 * @param func
		 * @return
		 */
		public SelectSqlBuilder databaseFunc(DatabaseFunc func)
		{
			this.func=func;
			return this;
		}
		
		/**
		 * 排除相同记录
		 */
		public SelectSqlBuilder distinct()
		{
			distinct=true;
			return this;
		}
		/**
		 * 选择当前表（from、xxJoin、target）所有字段。<br>
		 * 如果在from之前调用，则不带表前缀；如果有join表，最好明确哪些字段，而不是用此返回所有字段，
		 * 即使要返回所有字段，也要在from后或join后调用，以便明确是哪个表
		 * @return
		 */
		public SelectSqlBuilder fieldAll()
		{
			selectCols.add(new TableColumn(currentTarget, "*", null));
			return this;
		}
		/**
		 * 选择当前表（from、xxJoin、target）某个字段
		 * @param fld
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder field(String fld, String alias)
		{
			return field(new TableColumn(currentTarget, fld, alias));
		}
		/**
		 * 选择当前表（from、xxJoin、target）某个字段
		 * @param fld
		 * @return
		 */
		public SelectSqlBuilder field(String fld)
		{
			return field(new TableColumn(currentTarget, fld, null));
		}
		/**
		 * 选择指定表某个字段
		 * @param col
		 * @return
		 */
		public SelectSqlBuilder field(TableColumn col)
		{
			selectCols.add(col);
			return this;
		}
		/**
		 * 选择一个复杂子句构成的字段
		 * @param item
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder field(ClauseItem item, String alias)
		{
			selectCols.add(new ClauseColumn(item,alias));
			return this;
		}
		/**
		 * 由后续的内嵌表达式构造为返回的字段值，以toSel()结束
		 * @param alias
		 * @return
		 */
		public ClauseBuilder fieldAs(String alias)
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_FIELD;

			selectCols.add(new ClauseColumn(null,alias));
			return conditionBuilder;
		}
		/**
		 * 查询来源表
		 * @param tbl
		 * @return
		 */
		public SelectSqlBuilder from(Table tbl)
		{
			fromTable=tbl;
			currentTarget=tbl;
			return this;
		}
		/**
		 * 查询来源表
		 * @param tblName
		 * @return
		 */
		public SelectSqlBuilder from(String tblName)
		{
			return from(createTable(tblName, null));
		}
		/**
		 * 查询来源表
		 * @param tblName
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder from(String tblName, String alias)
		{
			return from(createTable(tblName, alias));
		}
		
		/**
		 * 左连接表
		 * @param tblName
		 * @return
		 */
		public SelectSqlBuilder leftJoin(String tblName)
		{
			return leftJoin(createTable(tblName));
		}
		/**
		 * 左连接表
		 * @param tblName
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder leftJoin(String tblName, String alias)
		{
			return leftJoin(createTable(tblName,alias));
		}
		/**
		 * 左连接表
		 * @param tbl
		 * @return
		 */
		public SelectSqlBuilder leftJoin(Table tbl)
		{
			currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("left", tbl, null));
			return this;
		}
		/**
		 * 内连接表
		 * @param tblName
		 * @return
		 */
		public SelectSqlBuilder innerJoin(String tblName)
		{
			return innerJoin(createTable(tblName));
		}
		/**
		 * 内连接表
		 * @param tblName
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder innerJoin(String tblName, String alias)
		{
			return innerJoin(createTable(tblName, alias));
		}
		/**
		 * 内连接表
		 * @param tbl
		 * @return
		 */
		public SelectSqlBuilder innerJoin(Table tbl)
		{
			currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("inner", tbl, null));
			return this;
		}
		/**
		 * 右连接表
		 * @param tblName
		 * @return
		 */
		public SelectSqlBuilder rightJoin(String tblName)
		{
			return rightJoin(createTable(tblName));
		}
		/**
		 * 右连接表
		 * @param tblName
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder rightJoin(String tblName, String alias)
		{
			return rightJoin(createTable(tblName, alias));
		}
		/**
		 * 右连接表
		 * @param tbl
		 * @return
		 */
		public SelectSqlBuilder rightJoin(Table tbl)
		{
			currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("right", tbl, null));
			return this;
		}
		/**
		 * 全连接表
		 * @param tblName
		 * @return
		 */
		public SelectSqlBuilder fullJoin(String tblName)
		{
			return fullJoin(createTable(tblName));
		}
		/**
		 * 全连接表
		 * @param tblName
		 * @param alias
		 * @return
		 */
		public SelectSqlBuilder fullJoin(String tblName, String alias)
		{
			return fullJoin(createTable(tblName, alias));
		}
		/**
		 * 全连接表
		 * @param tbl
		 * @return
		 */
		public SelectSqlBuilder fullJoin(Table tbl)
		{
			currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("full", tbl, null));
			return this;
		}
		
		/**
		 * 将on条件关联到调用此方法前的最后一个join表上
		 * @param cond
		 * @return
		 */
		public SelectSqlBuilder on(ClauseItem cond)
		{
			int index=getJoinTables().size()-1;
			JoinTableInfo item=getJoinTables().get(index);
			item.on=cond;
			return this;
		}
		/**
		 * 将后续内嵌的条件构建为on条件，用toSel()结束
		 * @return
		 */
		public ClauseBuilder onAs()
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_ON;
			return conditionBuilder;
		}
		private ClauseBuilder createClauseBuilder()
		{
			ClauseBuilder cb=new ClauseBuilder(this, (t)-> {
				if(conditionType==COND_ON) {
					on(t);
				}else if(conditionType==COND_WHERE) {
					where(t);
				}else if(conditionType==COND_HAVING) {
					having(t);
				}else if(conditionType==COND_FIELD) {
					int inx=selectCols.size()-1;
					ClauseColumn cc=(ClauseColumn)selectCols.get(inx);
					cc.setColumn(t);
				}
			});
			return cb;
		}
		/**
		 * 将当前表切换为from表
		 * @return
		 */
		public SelectSqlBuilder target()
		{
			currentTarget=fromTable;
			return this;
		}
		
		/**
		 * 将当前表切换到指定表（可设为空）
		 * @param tbl 如果为空，表示字段不带表前缀；如果不为空，此表应该至少用from、xxJoin子句添加过
		 * @return
		 */
		public SelectSqlBuilder target(Table tbl)
		{
			currentTarget=tbl;
			return this;
		}
		/**
		 * 将当前表切换到指定表，如果没有匹配到，会抛出表不存在异常
		 * @param tblName 要切换的表名，或别名（不区分大小写）。此表应该至少用from、xxJoin子句添加过
		 * @return
		 */
		public SelectSqlBuilder target(String tblName)
		{
			if(fromTable.matchName(tblName)) {
				currentTarget=fromTable;
			} else {
				boolean find=false;
				for(JoinTableInfo jtf:getJoinTables())
				{
					if(jtf.table.matchName(tblName)) {
						find=true;
						currentTarget=jtf.table;
						break;
					}
				}
				if(!find) 
					throw new TableNotExistException(tblName);
			}
			return this;
		}
		
		/**
		 * 查询条件
		 * @param cond
		 * @return
		 */
		public SelectSqlBuilder where(ClauseItem cond)
		{
			this.where=cond;
			return this;
		}
		/**
		 * 将后续内嵌的条件构建为where条件，用toSel()结束
		 * @return
		 */
		public ClauseBuilder whereAs()
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_WHERE;
			return conditionBuilder;
		}
		
		/**
		 * 基于当前表（from、xxJoin、target）的指定字段分组
		 * @param fld
		 * @return
		 */
		public SelectSqlBuilder groupBy(String fld)
		{
			return groupBy(new TableColumn(currentTarget, fld, null));
		}
		/**
		 * 基于指定字段分组
		 * @param col
		 * @return
		 */
		public SelectSqlBuilder groupBy(TableColumn col)
		{
			getGroupCols().add(col);
			return this;
		}
		/**
		 * 分组过滤条件
		 * @param cond
		 * @return
		 */
		public SelectSqlBuilder having(ClauseItem cond)
		{
			this.having=cond;
			return this;
		}
		/**
		 * 将后续内嵌的条件构建为having条件，用toSel()结束
		 * @return
		 */
		public ClauseBuilder havingAs()
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_HAVING;
			return conditionBuilder;
		}

		/**
		 * 获得union的最后一层
		 * @return
		 */
		private SelectSqlBuilder getLeaf()
		{
			if(unionChild!=null)
				return unionChild.getLeaf();
			return this;
		}

		/**
		 * 获得union的最上一层
		 * @return
		 */
		private SelectSqlBuilder getRoot()
		{
			if(unionParent!=null)
				return unionParent.getRoot();
			return this;
		}

		/**
		 * 联合另一个查询的不同记录
		 * @return
		 */
		public SelectSqlBuilder union()
		{
			SelectSqlBuilder prev=getLeaf();
			SelectSqlBuilder sql=new SelectSqlBuilder(prev,false);
			return sql;
		}
		/**
		 * 联合另一个查询的所有记录
		 * @return
		 */
		public SelectSqlBuilder unionAll()
		{
			SelectSqlBuilder prev=getLeaf();
			SelectSqlBuilder sql=new SelectSqlBuilder(prev,true);
			return sql;
		}

		/**
		 * 基于当前表（from、xxJoin、target）的指定字段按升序排序
		 * @param fld
		 * @return
		 */
		public SelectSqlBuilder orderBy(String fld)
		{
			return orderBy(new TableColumn(currentTarget, fld, null), false);
		}
		/**
		 * 基于当前表（from、xxJoin、target）的指定字段排序
		 * @param fld
		 * @param desc 是否降序
		 * @return
		 */
		public SelectSqlBuilder orderBy(String fld, boolean desc)
		{
			return orderBy(new TableColumn(currentTarget, fld, null), desc);
		}
		/**
		 * 基于指定字段排序
		 * @param col
		 * @return
		 */
		public SelectSqlBuilder orderBy(TableColumn col)
		{
			return orderBy(col, false);
		}
		/**
		 * 基于指定字段排序
		 * @param col
		 * @param desc
		 * @return
		 */
		public SelectSqlBuilder orderBy(TableColumn col, boolean desc)
		{
			SelectSqlBuilder root=getRoot();
			root.getOrderCols().add(Pair.of(col, desc?"DESC":null));
			return root;
		}

		/**
		 * 构建查询sql语句
		 * @return
		 */
		public String build()
		{
			SelectSqlBuilder root=getRoot();
			StringBuilder sb=new StringBuilder();
			if(root.func==null && this.func!=null)
				root.func=this.func;
			root.buildInternal(sb);
			return sb.toString();
		}
		private void buildInternal(StringBuilder sb)
		{
			sb.append("select ");
			if(distinct)
				sb.append("DISTINCT ");
			boolean first=true;
			if(!CollectionUtils.isEmpty(selectCols)) {
				for(TableColumn col:selectCols) {
					if(!first) sb.append(",");
					else first=false;
					//处理特殊的列
					if(col instanceof ClauseColumn) {
						ClauseColumn cc=(ClauseColumn)col;
						cc.func=this.func;
						cc.toSql(BUILD_SECTION_SEUPDATE, sb);
					}else {
						col.toSql(0, sb);
					}
				}
			}else {
				sb.append("*");
			}
			
			sb.append(" from ");
			if(fromTable==null)
				throw new TableNotExistException("null");
			fromTable.setDatabaseFunc(func);
			fromTable.toSql(BUILD_SECTION_FROMJOIN,sb);
			
			if(!CollectionUtils.isEmpty(joinTables)) {
				for(JoinTableInfo it:joinTables) {
					sb.append(" ").append(it.joinType).append(" join ");
					it.table.setDatabaseFunc(func);
					it.table.toSql(BUILD_SECTION_FROMJOIN, sb);
					sb.append(" on ");
					it.on.setDatabaseFunc(func);
					it.on.toSql(sb);
				}
			}
			
			if(where!=null) {
				sb.append(" where ");
				where.setDatabaseFunc(func);
				where.toSql(sb);
			}
			
			if(!CollectionUtils.isEmpty(groupCols)) {
				first=true;
				sb.append(" group by ");
				for(TableColumn col:groupCols) {
					if(!first) sb.append(",");
					else first=false;
					col.toSql(2, sb);
				}
				if(having!=null) {
					sb.append(" having ");
					having.setDatabaseFunc(func);
					having.toSql(sb);
				}
			}
			
			if(unionChild!=null) {
				sb.append("\n union ");
				if(unionAll)
					sb.append("all ");
				sb.append("\n");
				
				unionChild.func=this.func;
				unionChild.buildInternal(sb);
			}
			//由于添加order by时，总是在root上添加，因此union的子查询不会有orderCols
			if(!CollectionUtils.isEmpty(orderCols)) {
				first=true;
				sb.append(" order by ");
				for(Pair<TableColumn, String> it:orderCols) {
					if(!first) sb.append(",");
					else first=false;
					it.getKey().toSql(2, sb);
					if(!StringUtils.isEmpty(it.getValue()))
						sb.append(" ").append(it.getValue());
				}
			}
		}
	}
	
	//README 更新语句构造器定义
	/**
	 * 更新语句构造器定义
	 * @author dnnyo
	 *
	 */
	public static class UpdateSqlBuilder
	{
		DatabaseFunc func;
		
		int conditionType=0;
		ClauseBuilder conditionBuilder=null;
	
		Table intoTable;
		LinkedCaseInsensitiveMap<SingleValue> setValues=new LinkedCaseInsensitiveMap<>();
		//(dennis)暂不支持连表更新
		//List<JoinTableInfo> joinTables=null;
		//Table currentTarget;
		ClauseItem where;
		
		/**
		 * 获得当前用于转换函数调用的适配类
		 * @return
		 */
		public DatabaseFunc getDatabaseFunc()
		{
			return func;
		}
		/**
		 * 用于转换函数调用的适配类
		 * @param func
		 * @return
		 */
		public UpdateSqlBuilder databaseFunc(DatabaseFunc func)
		{
			this.func=func;
			return this;
		}

//		private List<JoinTableInfo> getJoinTables()
//		{
//			if(joinTables==null)
//				joinTables=new ArrayList<>();
//			return joinTables;
//		}
		
		public UpdateSqlBuilder(Table tbl)
		{
			intoTable=tbl;
		}
		
		public UpdateSqlBuilder(String tblName)
		{
			this(SqlBuilder.createTable(tblName, null));
		}
		
		/**
		 * 设置要更新的字段值
		 * @param col
		 * @param value 常量值（如果与查询参数前缀冲突，则要用反引号`包含，
		 *  说明参见：{@link ClauseBuilder#valueToCond(Object)}）、查询参数、SingleValue对象
		 * @return
		 */
		public UpdateSqlBuilder set(String col, Object value)
		{
			setValues.put(col, ClauseBuilder.valueToCond(value));
			return this;
		}
		/**
		 * 由后面内嵌构造的表达式设置为一个字段值，表达式由toUpd()返回
		 * @param col
		 * @return
		 */
		public ClauseBuilder setAs(String col)
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_FIELD;
			
			setValues.put(col, null);
			return conditionBuilder;
		}
		/**
		 * 设置多个字段值
		 * @param values
		 * @return
		 */
		public UpdateSqlBuilder set(Map<String, Object> values)
		{
			for(Map.Entry<String, Object> ent:values.entrySet())
				setValues.put(ent.getKey(), ClauseBuilder.valueToCond(ent.getValue()));
			return this;
		}
		
		
		/**
		 * 依次设置字段名，后面调用values可对等设置值，或select设置查询结果
		 * @param cols
		 * @return
		 */
		public UpdateSqlBuilder fields(String... cols)
		{
			for(String col:cols)
				setValues.put(col, null);
			return this;
		}
		
		/**
		 * 依次设置字段值，与fields右对齐
		 * @param vals 每个元素可以是封装的SingleValue，也可以是原始值：参数符、常量值等
		 * @return
		 */
		public UpdateSqlBuilder values(Object... vals)
		{
			int istart=setValues.size()-vals.length;
			if(istart<0) istart=0;//如果参数超过字段项，则从左对齐
			int inx=0;
			for(Map.Entry<String, SingleValue> ent:setValues.entrySet()) {
				if(inx==istart) {
					ent.setValue(ClauseBuilder.valueToCond(vals[istart]));
					istart++;
				}
				inx++;
			}
			return this;
		}

		//(dennis)暂不支持表连接更新
		/*
		public UpdateSqlBuilder leftJoin(String tblName)
		{
			return leftJoin(createTable(tblName));
		}
		public UpdateSqlBuilder leftJoin(Table tbl)
		{
			//currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("left", tbl, null));
			return this;
		}
		
		public UpdateSqlBuilder innerJoin(String tblName)
		{
			return innerJoin(createTable(tblName));
		}
		public UpdateSqlBuilder innerJoin(Table tbl)
		{
			//currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("inner", tbl, null));
			return this;
		}
		
		public UpdateSqlBuilder rightJoin(String tblName)
		{
			return rightJoin(createTable(tblName));
		}
		public UpdateSqlBuilder rightJoin(Table tbl)
		{
			//currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("right", tbl, null));
			return this;
		}
		
		public UpdateSqlBuilder fullJoin(String tblName)
		{
			return fullJoin(createTable(tblName));
		}
		public UpdateSqlBuilder fullJoin(Table tbl)
		{
			//currentTarget=tbl;
			getJoinTables().add(new JoinTableInfo("full", tbl, null));
			return this;
		}
		
		public UpdateSqlBuilder on(Condition cond)
		{
			int index=getJoinTables().size()-1;
			JoinTableInfo item=getJoinTables().get(index);
			item.on=cond;
			return this;
		}
		//*/
		/**
		 * 更新条件
		 * @param cond
		 * @return
		 */
		public UpdateSqlBuilder where(ClauseItem cond)
		{
			this.where=cond;
			return this;
		}
		/**
		 * 将后续内嵌的条件构建为where条件，用toUpd()结束
		 * @return
		 */
		public ClauseBuilder whereAs()
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_WHERE;
			return conditionBuilder;
		}
		private ClauseBuilder createClauseBuilder()
		{
			ClauseBuilder cb=new ClauseBuilder(this, (t)-> {
				if(conditionType==COND_WHERE) {
					where(t);
//				}else if(conditionType==COND_HAVING) {
//					having(t);
//				}else if(conditionType==COND_ON) {
//					on(t);
				}else if(conditionType==COND_FIELD) {
					Map.Entry<String, SingleValue> lastEnt=null;
					for(Map.Entry<String, SingleValue> ent:setValues.entrySet())
					{
						lastEnt=ent;
					}
					lastEnt.setValue((SingleValue)t);
				}
			});
			return cb;
		}
		
		/**
		 * 构造更新sql语句
		 * @return
		 */
		public String build()
		{
			StringBuilder sb=new StringBuilder();
			sb.append("update ");
			
			intoTable.setDatabaseFunc(func);
			intoTable.toSql(SqlBuilder.BUILD_SECTION_FROMJOIN,sb);
			
			sb.append(" set ");
			boolean first=true;
			for(Map.Entry<String, SingleValue> ent:setValues.entrySet())
			{
				if(!first) {
					sb.append(",");
				}else {
					first=false;
				}
				sb.append(ent.getKey()).append("=");
				ent.getValue().setDatabaseFunc(func);
				ent.getValue().toSql(sb);
			}
			
			//oracle:update t1 set (a,b,c)=(select a,b,c from t2 where t2.id=t1.id)
			//mysql:update t1 inner join t2 on.. set.. where..
			//postgre:update t1 set.. from t2,t3 where..
//			if(!CheckUtil.isNullorEmpty(joinTables)) {
//				for(JoinTableInfo it:joinTables) {
//					sb.append(" ").append(it.joinType).append(" join ");
//					it.table.setDatabaseFunc(func);
//					it.table.toSql(BUILD_SECTION_JOIN, sb);
//					sb.append(" on ");
//					it.on.setDatabaseFunc(func);
//					it.on.toSql(sb);
//				}
//			}

			if(where!=null) {
				sb.append(" where ");
				where.setDatabaseFunc(func);
				where.toSql(sb);
			}
			
			return sb.toString();
		}
	}
	
	//README 插入语句构造器定义
	/**
	 * 插入语句构造器定义
	 * @author dnnyo
	 *
	 */
	public static class InsertSqlBuilder
	{
		DatabaseFunc func;
		
		int conditionType=0;
		ClauseBuilder conditionBuilder=null;
		
		Table intoTable;
		LinkedCaseInsensitiveMap<SingleValue> setValues=new LinkedCaseInsensitiveMap<>();
		SelectSqlBuilder selectBuilder=null;
		
		/**
		 * 获得当前用于转换函数调用的适配类
		 * @return
		 */
		public DatabaseFunc getDatabaseFunc()
		{
			return func;
		}
		/**
		 * 用于转换函数调用的适配类
		 * @param func
		 * @return
		 */
		public InsertSqlBuilder databaseFunc(DatabaseFunc func)
		{
			this.func=func;
			return this;
		}

		/**
		 * 插入表
		 * @param tbl
		 * @return
		 */
		public InsertSqlBuilder into(Table tbl)
		{
			this.intoTable=tbl;
			return this;
		}
		/**
		 * 插入表
		 * @param tblName
		 * @return
		 */
		public InsertSqlBuilder into(String tblName)
		{
			return into(createTable(tblName, null));
		}
		
		/**
		 * 要设置值的列
		 * @param col
		 * @param value 可以是封装的SingleValue，也可以是原始值：参数符、常量值等
		 * @return
		 */
		public InsertSqlBuilder set(String col, Object value)
		{
			setValues.put(col, ClauseBuilder.valueToCond(value));
			return this;
		}
		/**
		 * 由后面内嵌构造的表达式设置为一个字段值，表达式由toIns()返回
		 * @param col
		 * @return
		 */
		public ClauseBuilder setAs(String col)
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_FIELD;
			
			setValues.put(col, null);
			return conditionBuilder;
		}
		private ClauseBuilder createClauseBuilder()
		{
			ClauseBuilder cb=new ClauseBuilder(this, (t)-> {
//				if(conditionType==COND_WHERE) {
//					where(t);
//				}else if(conditionType==COND_HAVING) {
//					having(t);
//				}else if(conditionType==COND_ON) {
//					on(t);
//				}else
				if(conditionType==COND_FIELD) {
					Map.Entry<String, SingleValue> lastEnt=null;
					for(Map.Entry<String, SingleValue> ent:setValues.entrySet())
					{
						lastEnt=ent;
					}
					lastEnt.setValue((SingleValue)t);
				}
			});
			return cb;
		}

		/**
		 * 设置多个字段值
		 * @param values
		 * @return
		 */
		public InsertSqlBuilder set(Map<String, Object> values)
		{
			for(Map.Entry<String, Object> ent:values.entrySet())
				setValues.put(ent.getKey(), ClauseBuilder.valueToCond(ent.getValue()));
			return this;
		}
		
		/**
		 * 依次设置字段名，后面调用values可对等设置值，或select设置查询结果
		 * @param cols
		 * @return
		 */
		public InsertSqlBuilder fields(String... cols)
		{
			for(String col:cols)
				setValues.put(col, null);
			return this;
		}
		
		/**
		 * 依次设置字段值，与fields右对齐
		 * @param vals 每个元素可以是封装的SingleValue，也可以是原始值：参数符、常量值等
		 * @return
		 */
		public InsertSqlBuilder values(Object... vals)
		{
			int istart=setValues.size()-vals.length;
			if(istart<0) istart=0;//如果参数超过字段项，则从左对齐
			int inx=0;
			for(Map.Entry<String, SingleValue> ent:setValues.entrySet()) {
				if(inx==istart) {
					ent.setValue(ClauseBuilder.valueToCond(vals[istart]));
					istart++;
				}
				inx++;
			}
			return this;
		}
		/**
		 * 设置值来源于查询sql结果，不能与set共用
		 * @param selectBuilder
		 * @return
		 */
		public InsertSqlBuilder select(SelectSqlBuilder selectBuilder)
		{
			this.selectBuilder=selectBuilder;
			return this;
		}

		/**
		 * 构建插入sql语句
		 * @return
		 */
		public String build()
		{
			StringBuilder sb=new StringBuilder();
			sb.append("insert into ");
			intoTable.setDatabaseFunc(func);
			intoTable.toSql(SqlBuilder.BUILD_SECTION_DEINSERT,sb);
			
			boolean bsql=selectBuilder!=null;
			sb.append("(");
			StringBuilder sbValue=new StringBuilder();
			boolean first=true;
			for(Map.Entry<String, SingleValue> ent:setValues.entrySet())
			{
				if(!first) {
					sb.append(",");
					if(!bsql)
						sbValue.append(",");
				}else {
					first=false;
				}
				sb.append(ent.getKey());
				if(!bsql) {
					ent.getValue().setDatabaseFunc(func);
					ent.getValue().toSql(sbValue);
				}
			}
			sb.append(") ");
			
			if(!bsql) {
				sb.append("values (").append(sbValue).append(")");
			} else {
				selectBuilder.databaseFunc(func);
				sb.append(selectBuilder.build());
			}
			
			return sb.toString();
		}
	}
	
	//README 删除语句构造器定义
	/**
	 * 删除语句构造器定义
	 * @author dnnyo
	 *
	 */
	public static class DeleteSqlBuilder
	{
		DatabaseFunc func;
		
		int conditionType=0;
		ClauseBuilder conditionBuilder=null;
		
		Table fromTable;
		ClauseItem where;
		
		/**
		 * 获得当前用于转换函数调用的适配类
		 * @return
		 */
		public DatabaseFunc getDatabaseFunc()
		{
			return func;
		}
		/**
		 * 用于转换函数调用的适配类
		 * @param func
		 * @return
		 */
		public DeleteSqlBuilder databaseFunc(DatabaseFunc func)
		{
			this.func=func;
			return this;
		}

		/**
		 * 删除记录的表
		 * @param tbl
		 * @return
		 */
		public DeleteSqlBuilder from(Table tbl)
		{
			this.fromTable=tbl;
			return this;
		}
		/**
		 * 删除记录的表
		 * @param tblName
		 * @return
		 */
		public DeleteSqlBuilder from(String tblName)
		{
			return from(createTable(tblName, null));
		}
		/**
		 * 删除条件
		 * @param cond
		 * @return
		 */
		public DeleteSqlBuilder where(ClauseItem cond)
		{
			this.where=cond;
			return this;
		}
		/**
		 * 将后续内嵌的条件构建为where条件，用toDel()结束
		 * @return
		 */
		public ClauseBuilder whereAs()
		{
			if(conditionBuilder==null)
				conditionBuilder=createClauseBuilder();
			else
				conditionBuilder.reset();
			conditionType=COND_WHERE;
			return conditionBuilder;
		}
		private ClauseBuilder createClauseBuilder()
		{
			ClauseBuilder cb=new ClauseBuilder(this, (t)-> {
				if(conditionType==COND_WHERE) {
					where(t);
//				}else if(conditionType==COND_HAVING) {
//					having(t);
//				}else if(conditionType==COND_ON) {
//					on(t);
				}
			});
			return cb;
		}
		/**
		 * 构建删除sql语句
		 * @return
		 */
		public String build()
		{
			StringBuilder sb=new StringBuilder();
			sb.append("delete from ");
			
			fromTable.setDatabaseFunc(func);
			fromTable.toSql(SqlBuilder.BUILD_SECTION_DEINSERT,sb);
			
			if(where!=null) {
				sb.append(" where ");
				where.setDatabaseFunc(func);
				where.toSql(sb);
			}
			
			return sb.toString();
		}
	}
	
	/**
	 * 保存join表信息的辅助类
	 * @author dnnyo
	 *
	 */
	static class JoinTableInfo
	{
		String joinType;
		Table table;
		ClauseItem on;
		
		JoinTableInfo(String joinType, Table table, ClauseItem on)
		{
			if(StringUtils.isEmpty(joinType))
				joinType="inner";

			this.joinType=joinType;
			this.table=table;
			this.on=on;
		}
	}
}
